<?php
class Db{
    public $con; #链接标识
    public $logData = [];

    //初始化链接
    public function __construct($config=[]){
        if(!$config){
          #域名
          $host = explode(":",$_SERVER['HTTP_HOST']);
          $host = $host[0];
          #主域名
          $host = explode('.',$host);
          $len = count($host);
          $main_domain = strtolower($host[$len-2].'.'.$host[$len-1]);

          #域名里的配置
          $rs = include 'Env.php';
          foreach($rs['domain'] as $k => $v){
            if($k==$main_domain){
              $config = $v['db'];
              break;
            }
          }
        }
        $this->con=mysqli_connect($config['host'],$config['user'],$config['pwd'],$config['dbname']);
        if (!$this->con){
            die("连接错误: " . mysqli_connect_error());
        }else{
            mysqli_query($this->con,'set names utf8');
            mysqli_autocommit($this->con,true);
        }
        return $this;
    }

    //开始事务
    public function begin(){
        mysqli_query($this->con, "SET AUTOCOMMIT=0");
        mysqli_begin_transaction($this->con);
    }

    //执行事务
    public function end($arr){
        $check = true;
        foreach($arr as $v){
            if(!$v){
                $check = false;
                break;
            }
        }
        if($check){
            mysqli_commit($this->con);
        }else{
            mysqli_query($this->con, "ROLLBACK");
        }
        return $check;
    }

    //缓存sql
    public function cache($sql,$vars=[],$limit=''){
        return $this->query($sql,$vars,$limit,false,true);
    }

    //执行sql
    public function query($sql,$vars=[],$limit='', $isCount=false,$isCache=false){
        $sql = trim($sql);
        $sql = $this->escape($sql,$vars);
        $arr  = explode(" ",$sql);
        $type = trim(strtolower($arr[0]));
        switch ($type) {
            case 'select':
                $debug = true;
                #分页查询是没有缓存的
                if(false && $isCache && !$debug){
                    $rs = $this->doCache($sql,$limit);
                    return $rs;
                }else{
                    $result  = $this->dosql( $sql );
                    if($limit===1){
                        $rs = mysqli_fetch_array($result,MYSQLI_ASSOC);
                    }else{
                        $rs = mysqli_fetch_all($result, MYSQLI_ASSOC);
                        if($isCount){
                            if(strpos($sql,'group')===false){
                                $sql = preg_replace("/([\s\S]*)(order[\s]+by)([\s\S]*)/","$1",$sql);
                                $sql = preg_replace("/([\s\S]*)([\s]+limit[\s]+)([\s\S]*)/","$1",$sql);
                                $sql = preg_replace("/(select[\s]+)([\s\S]*)([\s]+from[\s]+)([\s\S]*)/","$1 count(*) as nums $3$4",$sql);
                            }else{
                                $sql = preg_replace("/([\s\S]*)(order[\s]+by)([\s\S]*)/","$1",$sql);
                                $sql = preg_replace("/([\s\S]*)([\s]+limit[\s]+)([\s\S]*)/","$1",$sql);
                                $sql = "select count(*) as nums from ($sql) as a";
                            }
                            $count = mysqli_fetch_array($this->dosql($sql),MYSQLI_ASSOC);
                            $rs = [
                                $rs,
                                $count['nums'],
                            ];
                        }
                    }
                    mysqli_free_result($result);
                    return $rs;
                }
                break;
            case 'update':
                $result  = $this->dosql( $sql );
                return mysqli_affected_rows($this->con);
                break;
            case 'delete':
                $result  = $this->dosql( $sql );
                return mysqli_affected_rows($this->con);
                break;
            case 'insert':
                $result  = $this->dosql( $sql );
                return mysqli_insert_id($this->con);
                break;
            default:
                die('sql 错误');
                break;
        }
    }
    //查找单条数据
    public function find($table,$id='', $fields='*'){
        $id = (int)$this->filter($id);
        $sql = "select {$fields} from `{$table}` WHERE id = {$id}";
        $result = $this->dosql($sql);
        $rs = mysqli_fetch_array($result,MYSQLI_ASSOC);
        mysqli_free_result($result);
        return $rs;
    }
    /**
     * 查找数据,二维
     * @param  [type] $table  [表名称]
     * @param  string $where  [查找条件]
     * $where 格式:  ['sex=? and name=?', [1, '张三']]
     * @param  string $fields [查找字段]
     * @return [type]         [description]
     */
    public function select($table,$where=[], $fields='*'){
        $sql = "select {$fields} from `{$table}` ";
        if($where){
            $sql .= ' where '.$where[0];
            $sql = $this->escape($sql, $where[1]);
        }
        $result = $this->dosql($sql);
        $rs = mysqli_fetch_all($result,MYSQLI_ASSOC);
        mysqli_free_result($result);
        return $rs;

    }
    /**
     * 更新表,
     * @param  [type] $table [表名称]
     * @param  [type] $data  [更新数组,键值对]
     * @param  array  $where [更新条件]
     * $where 格式:  ['sex=? and name=?', [1, '张三']]
     * @return [type]        [description]
     */
    public function update($table, $data, $where=[]){
        $temp = [];
        foreach ($data as $k => $v){
            $k = $this->filter($k);
            $vv = $this->filter($v);
            $vstr = is_string($v) ? "'{$vv}'":(int)$vv;
            array_push($temp, "`$k`=$vstr");
        }
        $kav = implode(',', $temp);
        $sql = "update `{$table}` set {$kav} ";
        #id作为更新条件
        if(isset($data['id']) && !$where){
            $data['id'] = (int)$this->filter($data['id']);
            $sql .= " where id={$data['id']}";
        }else{
            if($where){
                $sql .= ' where '.$where[0];
                $sql = $this->escape($sql, $where[1]);
            }else{
                die('update快捷方式不能缺少条件');
            }
        }
        $result = $this->dosql($sql);
        return mysqli_affected_rows($this->con);
    }
    //插入
    public function insert($table, $data){
        $values = "";
        $keys = [];
        foreach($data as $k => $v){
            $keys[]= $this->filter($k);
            $vv = $this->filter($v);
            $values .= is_string($v) || empty($vv) ? "'{$vv}'," : "{$vv},";
        }
        $values = rtrim($values,',');
        $sql = "insert into `{$table}` (`".implode('`,`', $keys)."`) values (".$values.")";
        $result = $this->dosql($sql);
        return mysqli_insert_id($this->con);
    }
    //插入多条
    public function insertAll($table, $data){
        $values = "";
        foreach($data as $k => $v){
            $keys = [];
            $db_val = "(";
            foreach($v as $key => $val){
                $keys[] = "`{$key}`";
                $vv = $this->filter($val);
                $db_val .= is_string($val) ? "'{$vv}'," : "{$vv},";
            }
            $db_val = rtrim($db_val,",");
            $db_val .= "),";
            $values .= $db_val;
        }
        $values = rtrim($values,",");
        $sql = "insert into `{$table}` (".implode(',',$keys).") values ".$values;
        $result = $this->dosql($sql);
        return mysqli_insert_id($this->con);
    }
    //删除
    public function delete($table,$val='' ,$fields='id'){
        $val = $this->filter($val);
        if ($fields == 'id') {
            $val = (int)$val;
            $sql = "delete from `{$table}` WHERE `$fields`={$val}";
        }else{
            $sql = "delete from `{$table}` WHERE `$fields`='{$val}'";
        }
        $this->dosql($sql);
        return mysqli_affected_rows($this->con);
    }

    //组装占位符, 并过滤参数
    public function escape($sql,$vars){
        foreach($vars as $v){
            $str = $this->filter($v);
            if(is_string($v)){
                $sql = preg_replace('/\?/',"'{$str}'",$sql,1);
            }else{
                $str = (int)$str;
                $sql = preg_replace('/\?/',"{$str}",$sql,1);
            }
        }
        return strtolower(trim($sql));
    }

    //执行sql,并写缓存
    public function doCache($sql,$limit=''){

        //查找出sql目录
        $dir = scandir(APP_PATH.'../public/uploads/cache');
        $sqldir = "";
        foreach($dir as $v){
            if(strpos($v,'sql')!==false){
                $sqldir = $v;
                break;
            }
        }
        if(!$sqldir){
            $sqldir = APP_PATH.'../public/uploads/cache/sql'.time();
            if(!com_mkdir($sqldir)){
                die('No permission to create a cache directory');
            }
        }else{
            $time = str_replace("sql","",$sqldir);
            if($time<time()-24*3600){
                File::remove_dir(APP_PATH.'../public/uploads/cache',true);
                $sqldir = APP_PATH.'../public/uploads/cache/sql'.time();
                if(!com_mkdir($sqldir)){
                    die('No permission to create a cache directory');
                }
            }else{
                $sqldir = APP_PATH.'../public/uploads/cache/'.$sqldir;
            }
        }
        //查找sql文件
        $dir = scandir($sqldir);
        $sqlfile = md5(str_replace([' ',',','\\','\/'],"_",$sql).$limit).".txt";
        $check = false;
        foreach($dir as $v){
            if($v==$sqlfile){
                $check = true;
                break;
            }
        }
        $file = $sqldir.'/'.$sqlfile;
        //如果没有则创建缓存文件
        if(!$check){
            $myfile = fopen($file, "w") or die("No permission to write to the cache file");

            //执行sql
            $result  = $this->dosql( $sql );
            if($limit===1){
                $rs = mysqli_fetch_array($result,MYSQLI_ASSOC);
            }else{
                $rs = mysqli_fetch_all($result, MYSQLI_ASSOC);
            }
            mysqli_free_result($result);

            //写入文件
            $txt = json_encode($rs,JSON_UNESCAPED_UNICODE);
            fwrite($myfile, $txt);
            fclose($myfile);
        }else{
            $txt = file_get_contents($file);
        }
        $txt = json_decode($txt,true);
        return $txt;
    }

    //过滤参数
    public function filter($val){
        $d = ['select','union','update ','insert','delete'];
        $val = str_ireplace($d,'',$val);
        $val =  mysqli_escape_string($this->con,$val);
        return $val;
    }

    //执行sql, 记录时间
    public function dosql($sql){
        $debug = true;

        if($debug){
            $start  = microtime(true);
            $result = mysqli_query($this->con,$sql);
            $t      = bcsub(microtime(true),$start,5);
        }else{
            $result = mysqli_query($this->con,$sql);
            $t      = '';
        }

        $this->logData[] = "$sql  :  $t";
        if($result===false){
          die('sql error');
            //die("执行失败:$sql\r\n 失败原因：".mysqli_error($this->con));
        }
        return $result;
    }
    //显示sql
    public function getSql(){
        print_r($this->logData);
    }
    public function close(){
        @mysqli_close($this->con);
    }
    public function __destruct(){
        $this->close();
    }
    public function __clone(){
        die('Clone is not allow!');
    }
}
